package com.ckv.qcm.model.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.ckv.qcm.model.Question;
import com.ckv.qcm.model.Section;

public class SectionDAO extends DAO {

	public ArrayList<Long> getSectionIds(Long idTest) throws SQLException
	{
		
		ArrayList<Long> ret=new ArrayList<Long>();
		try
		{
			ResultSet result=this.connection.createStatement().executeQuery(
					"SELECT SECTIONS.idSection FROM SECTIONS" +
					" INNER JOIN TEST ON TEST.idTest=SECTIONS.idTest" +
					" WHERE idTest=" + idTest);
			
			if (result.next())
			{
				ret.add(result.getLong("idSection"));
			}
		}catch (SQLException e)
		{
			
			e.printStackTrace();
			throw e;
		}
		return ret;
		
	}
	
	
	public Section find(long id) {
		Section section = new Section();
		try {
			ResultSet result = this.connection.createStatement().executeQuery(
					"SELECT * FROM Sections WHERE idSection = " + id);
			if (result.next()) {
				// section = new Section(id,
				// result.getLong("idTest"),result.getInt("numeroOrdre"),result.getString("nom"),result.getInt("nombreQuestion"),result.getString("sujet"));
				section.setId(id);
				section.setIdTest(result.getLong("idTest"));
				section.setorderNumber(result.getInt("numeroOrdre"));
				section.setName(result.getString("nom"));
				section.setNbQuestions(result.getInt("nombreQuestion"));
				section.setSubject(result.getString("sujet"));

				ArrayList<Question> questions = new ArrayList<Question>();

				result = this.connection
						.createStatement()
						.executeQuery(
								"SELECT Questions.idQuestion FROM Questions INNER JOIN Rel_Question_Section ON Rel_Question_Section.idQuestion =Questions.idQuestion INNER JOIN Sections ON Sections.idSection=Rel_Question_Section.idSection WHERE Sections.idSection=" + result.getLong("idSection"));
				while (result.next()) {
					questions.add(Question.find(result.getLong("idQuestion")));

				}
				section.setQuestions(questions);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return section;
	}

	public ArrayList<Section> findAll() {

		ArrayList<Section> sections = new ArrayList<Section>();

		try {
			ResultSet result = this.connection.createStatement().executeQuery(
					"SELECT idSection FROM Section");
			while (result.next()) {
				sections.add(Section.find(result.getLong("idSection")));
			}
			return sections;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

	}

	public Section create(Section section) {
		PreparedStatement preparedStatement = null;
		ResultSet generatedKeys = null;

		try {

			preparedStatement = this.connection
					.prepareStatement(
							"INSERT INTO Sections (idTest,numeroOrdre,nom,nombreQuestion,sujet) VALUES (?,?,?,?,?)",
							Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setLong(1, section.getIdTest());
			preparedStatement.setInt(2, section.getorderNumber());
			preparedStatement.setString(3, section.getName());
			preparedStatement.setInt(4, section.getNbQuestions());
			preparedStatement.setString(5, section.getSubject());

			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0) {
				throw new SQLException(
						"Creating section failed, no rows affected.");
			}
			for (Question question : section.getQuestions()) {
				if (question.getId() == 0) {
					generatedKeys = preparedStatement.getGeneratedKeys();

					Question q = question.create();
					preparedStatement = this.connection
							.prepareStatement("INSERT INTO Rel_Question_Section (idQuestion,idSection) values(?,?)");
					preparedStatement.setLong(1, q.getId());
					preparedStatement.setLong(2, generatedKeys.getLong(1));

					affectedRows = preparedStatement.executeUpdate();
					if (affectedRows == 0) {
						throw new SQLException(
								"Creating Rel_Question_Section failed, no rows affected.");
					}
				}
			}

			generatedKeys = preparedStatement.getGeneratedKeys();
			if (generatedKeys.next()) {
				section = this.find(generatedKeys.getLong(1));
			} else {
				throw new SQLException(
						"Creating user failed, no generated key obtained.");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return section;

	}

	public Section update(Section section) {
		try {
			PreparedStatement prepareStatement = this.connection
					.prepareStatement("UPDATE Section SET numeroOrdre = ?, nom = ?, nombreQuestion = ?, sujet = ? WHERE idSection = ?");
			prepareStatement.setInt(1, section.getorderNumber());
			prepareStatement.setString(2, section.getName());
			prepareStatement.setInt(3, section.getNbQuestions());
			prepareStatement.setString(4, section.getSubject());

			int affectedRows = prepareStatement.executeUpdate();
			if (affectedRows == 0) {
				throw new SQLException(
						"Updating Section failed, no rows where updated.");
			}
			section = this.find(section.getId());

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return section;
	}

	public void delete(Section section) {
		try {
			this.connection.createStatement().executeUpdate(
					"DELETE FROM rel_Question_Section WHERE idSection="
							+ section.getId());

			this.connection.createStatement().executeUpdate(
					"DELETE FROM Section WHERE idSection = " + section.getId());

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

}
